package com.kakarote.crm9.erp.jxc.service;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.kakarote.crm9.erp.jxc.entity.GoodsSpecification;
import com.kakarote.crm9.utils.PageData;
import com.kakarote.crm9.utils.R;
import com.kakarote.crm9.utils.SqlUtil;

import java.util.List;

/**
 * @author xjl
 * @date 2021/3/15 16:54
 */
public class GoodsSpecificationService {

    /**
     * 查询商品规格表
     * @return
     */
    public List<Record> queryList(PageData pageData) {
        Integer pageNum = pageData.get("pageNum", Integer.class);
        Integer pageSize = pageData.get("pageSize", Integer.class);
        pageNum = (pageNum - 1) * pageSize;

        String sql = Db.getSql("jxc.goodsSpecification.queryList");
        Integer categorieId = pageData.get("categorieId", Integer.class);
        String specificationName = pageData.get("specificationName", String.class);

        StringBuilder builder = new StringBuilder();

        if (categorieId != null && specificationName != null) {
            sql += " where ";
            builder.append(" s.categorie_id = ?").append(" and s.specification_name like ?");
            builder.append(" order by s.categorie_id");
            builder.append(" LIMIT ?, ?");
            sql += builder.toString();
            String s = filterSqlKey(specificationName);
            return Db.find(sql, categorieId, "%" + s + "%", pageNum, pageSize);
        }
        if (categorieId != null && specificationName == null) {
            sql += " where ";
            builder.append(" s.categorie_id = ?");
            builder.append(" order by s.categorie_id");
            builder.append(" LIMIT ?, ?");
            sql += builder.toString();
            return Db.find(sql, categorieId, pageNum, pageSize);
        }
        if (categorieId == null && specificationName != null) {
            sql += " where ";
            builder.append(" s.specification_name like ?");
            builder.append(" order by s.categorie_id");
            builder.append(" LIMIT ?, ?");
            sql += builder.toString();
            String s = filterSqlKey(specificationName);
            return Db.find(sql, "%" + s + "%", pageNum, pageSize);
        }

        builder.append(" order by s.categorie_id");
        builder.append(" LIMIT ?, ?");
        sql += builder.toString();
        return Db.find(sql, pageNum, pageSize);
    }

    /**
     * 查询商品规格表
     * @return
     */
    public List<Record> queryList2(PageData pageData) {
        Integer pageNum = pageData.get("pageNum", Integer.class);
        Integer pageSize = pageData.get("pageSize", Integer.class);
        pageNum = (pageNum - 1) * pageSize;
        String sql = Db.getSql("jxc.goodsSpecification.queryList2");
        try {
            SqlUtil sqlUtil = new SqlUtil();
            String convertSql = sqlUtil.convert(sql, pageData);
            List<Object> params = sqlUtil.getParams();
            if (params != null && params.size() > 0) {
                params.add(pageNum);
                params.add(pageSize);
                return Db.find(convertSql, params.toArray());
            }
            return Db.find(convertSql, pageNum, pageSize);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 根据id查询
     * @param id
     * @return
     */
    public Record queryById(Integer id) {
        return Db.findFirst(Db.getSql("jxc.goodsSpecification.queryId"), id);
    }

    /**
     * 根据分类id和规格名查询
     * @param categorieId
     * @param specificationName
     * @return
     */
    public Record queryNameAndType(Integer categorieId, String specificationName) {
        return Db.findFirst(Db.getSql("jxc.goodsSpecification.queryNameAndType"), categorieId, specificationName);
    }

    /**
     * 查询记录数量
     * @return
     */
    public Record queryCount() {
        return Db.findFirst(Db.getSql("jxc.goodsSpecification.queryCount"));
    }

    /**
     * 添加
     * @param goodsSpecification
     * @return
     */
    public R insertSpecification(GoodsSpecification goodsSpecification) {
        return goodsSpecification.save() ? R.ok() : R.error();
    }

    /**
     * 更新
     * @param goodsSpecification
     * @return
     */
    public R updateSpecification(GoodsSpecification goodsSpecification) {
        return goodsSpecification.update() ? R.ok() : R.error();
    }

    /**
     * 删除
     * @param ids
     * @return
     */
    public R deleteSpecification(int[] ids) {
        int temp = 0;
        for (int id : ids) {
            temp += Db.delete(Db.getSql("jxc.goodsSpecification.deleteSpecificationById"), id);
        }
        return temp > 0 ? R.ok() : R.error();
    }

    /**
     * 过滤sql的通配符
     * @param sql
     * @return
     */
    private String filterSqlKey(String sql) {
        return sql.replaceAll("%", "")
                .replaceAll("\\^", "")
                .replaceAll("!", "")
                .replaceAll("\\[", "")
                .replaceAll("\\]", "");
    }
}
